/****** Object:  StoredProcedure [dbo].[USP_GXBZ_EMR_RYJL]    Script Date: 2018/12/5 19:11:07 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_GXBZ_EMR_RYJL]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[USP_GXBZ_EMR_RYJL]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/**********
[版本号]1.0.0.0.0
[创建时间]2018-12-05
[作者]WANG_W
[版权]卫宁健康科技集团股份有限公司
[描述] 
[功能说明]重庆cdc数据上传--入院记录GXBZ_EMR_RYJL数据上传
[返回值]
[调用的USP]
[调用实例] EXEC USP_GXBZ_EMR_RYJL
[修改历史]
1.0.1	周东东	2018-12-06	---添加字段ZYLSH主键约束
						          	---修改记录的日志表和时间戳表
1.0.1 周东东 2019-03-05  ---GXBZ_EMR_RYJL添加YLJGDM(医疗机构代码)和YLJGMC(医疗机构名称)字段
**********/
CREATE PROC [dbo].[USP_GXBZ_EMR_RYJL]    
AS 
  SET XACT_ABORT ON 
DECLARE
	--@sKssj VARCHAR(10)='',		--上传开始时间
	--@sJssj VARCHAR(10)=''		--上传结束时间
	@kssj datetime,					--上传开始时间
	@jssj datetime					--上传结束时间
BEGIN                                                                                                  

	
	IF NOT EXISTS(SELECT table_time FROM SYS_SJSC_TABLE 
	WHERE TABLE_NAME = 'GXBZ_EMR_RYJL')
	BEGIN 
		INSERT INTO SYS_SJSC_TABLE(TABLE_NAME,TABLE_TIME)
		VALUES('GXBZ_EMR_RYJL','2015-07-01')
	END 
	
	--获取已上传的时间
	SELECT @kssj =TABLE_TIME  FROM SYS_SJSC_TABLE(NOLOCK)
	WHERE TABLE_NAME = 'GXBZ_EMR_RYJL'
	
	--获取源表中最大上传时间
	SELECT @jssj = MAX(TBRQ) FROM ZYRYDJB(NOLOCK)
	

	IF(@jssj>ISNULL(@kssj,'1900-01-01'))
	 BEGIN
		BEGIN TRY
		BEGIN TRAN

	-------创建临时表  
	CREATE TABLE #TEMP_GXBZ_EMR_RYJL(
		ZYLSH varchar(64),
		GRJBXXBSH varchar(64),
		XGBZ varchar(1),
		CXJMJKDABH varchar(17),
		ZYHBS varchar(64),
		CJSJ datetime,
		JDYSGH varchar(64),
		JDYSXM varchar(32),
		BSCSZSFZH varchar(32),
		YHZDGXDM varchar(2),
		YHZDGXMC varchar(100),
		BSCSZXM varchar(30),
		YLJGDM varchar(22),
		YLJGMC varchar(70),
		WDGLJGDM varchar(22),
		WDGLJGMC varchar(70),
		ZZSHRGH varchar(64),
		ZZSHRXM varchar(30),
		JZYSGH varchar(64),
		JZYSXM varchar(30),
		ZYYSGH varchar(64),
		ZYYSXM varchar(30),
		ZZYSGH varchar(64),
		ZZYSXM varchar(30),
		RYSJ datetime,
		BCH varchar(10),
		BCHMC varchar(40),
		BFH varchar(10),
		BFHMC varchar(40),
		RYKSBM varchar(64),
		RYKSMC varchar(32),
		RYBQBM varchar(64),
		RYBQMC varchar(32),
		JZYYBM varchar(22),
		JZYYMC varchar(70),
		ZSMS varchar(1024),
		XBSMS varchar(1024),
		JWSBZ varchar(1),
		JWSMS varchar(1024),
		CRBBZ varchar(1),
		CRBMS varchar(1024),
		HYSMS varchar(1024),
		GMSMS varchar(1024),
		SSSMS varchar(1024),
		YFJZSMS varchar(1024),
		SXSMS varchar(1024),
		GRSMS varchar(1024),
		YJSMS varchar(1024),
		JZSMS varchar(1024),
		RYTW decimal(4,1),
		RYMB decimal(4,1),
		RYHXPL decimal(3,0),
		RYSSY decimal(3,0),
		RYSZY decimal(3,0),
		RYSG decimal(5,2),
		RYTZ decimal(5,2),
		YBZKJCJG varchar(1024),
		PFHNMJCJG varchar(1024),
		QSQMLBJCJG varchar(1024),
		TBJQQGJCJG varchar(1024),
		JBJCJG varchar(1024),
		XBJCJG varchar(1024),
		FBJCJG varchar(1024),
		GMZZJCJG varchar(1024),
		WSZQJCJG varchar(1024),
		JZJCJG varchar(1024),
		SZJCJG varchar(1024),
		SJXTJCJG varchar(1024),
		ZKQKMS varchar(1024),
		FZJCJG varchar(1024),
		CSNRKKBZ varchar(1024),
		XYCZDRQ datetime,
		XYCZDM varchar(10),
		XYCZMC varchar(100),
		XYCZRYZDSW int,
		ZYSZGCJG varchar(1024),
		ZYCZRQ datetime,
		ZYCZBM varchar(100),
		ZYCZBMDM varchar(10),
		ZYCZZHMC varchar(100),
		ZYCZZHDM varchar(10),
		ZYCZRYZDSW int,
		XYXZZDRQ datetime,
		XYXZZDMC varchar(100),
		XYXZZDDM varchar(10),
		XYXZRYZDSW int,
		ZYXZZDRQ datetime,
		ZYXZZDMC varchar(100),
		ZYXZZDDM varchar(10),
		ZYXZHZMC varchar(100),
		ZYXZHZDM varchar(10),
		ZYXZRYZDSW int,
		XYQDZDRQ datetime,
		XYQDZDMC varchar(100),
		XYQDZDDM varchar(10),
		XYQZZDSW int,
		ZYQDZDRQ datetime,
		ZYQDZDDM varchar(10),
		ZYQDZDMC varchar(100),
		ZYQDZHMC varchar(100),
		ZYQDZHDM varchar(10),
		ZYQZRYSW int,
		XYBCZDRQ datetime,
		XYBCZDMC varchar(100),
		XYBCZDDM varchar(10),
		XYBCZDRYSW int,
		ZZZF varchar(100),
		MJ varchar(16),
		TBRQ datetime,
		SCZT varchar(1),
		CS_JYBZ int,
		SCSJ datetime
	) ON [PRIMARY]

	--插业务数据到临时表
	INSERT INTO #TEMP_GXBZ_EMR_RYJL (ZYLSH,GRJBXXBSH, XGBZ, CXJMJKDABH, ZYHBS, CJSJ, JDYSGH, JDYSXM, BSCSZSFZH, YHZDGXDM, YHZDGXMC, BSCSZXM, YLJGDM,
		YLJGMC, WDGLJGDM, WDGLJGMC, ZZSHRGH, ZZSHRXM, JZYSGH, JZYSXM, ZYYSGH, ZYYSXM, ZZYSGH, ZZYSXM, RYSJ, BCH, BCHMC, BFH, BFHMC, RYKSBM, RYKSMC, RYBQBM,
		RYBQMC, JZYYBM, JZYYMC, ZSMS, XBSMS, JWSBZ, JWSMS, CRBBZ, CRBMS, HYSMS, GMSMS, SSSMS, YFJZSMS, SXSMS, GRSMS, YJSMS, JZSMS,
		RYTW, RYMB, RYHXPL, RYSSY, RYSZY, RYSG, RYTZ, YBZKJCJG, PFHNMJCJG, QSQMLBJCJG, TBJQQGJCJG, JBJCJG, XBJCJG, FBJCJG, GMZZJCJG,
		WSZQJCJG, JZJCJG, SZJCJG, SJXTJCJG, ZKQKMS, FZJCJG, CSNRKKBZ, XYCZDRQ, XYCZDM, XYCZMC, XYCZRYZDSW, ZYSZGCJG, ZYCZRQ, ZYCZBM,
		ZYCZBMDM, ZYCZZHMC, ZYCZZHDM, ZYCZRYZDSW, XYXZZDRQ, XYXZZDMC, XYXZZDDM, XYXZRYZDSW, ZYXZZDRQ, ZYXZZDMC, ZYXZZDDM, ZYXZHZMC,
		ZYXZHZDM, ZYXZRYZDSW,  XYQDZDMC,XYQDZDRQ, XYQDZDDM, XYQZZDSW, ZYQDZDRQ, ZYQDZDDM, ZYQDZDMC, ZYQDZHMC, ZYQDZHDM, ZYQZRYSW,
		XYBCZDRQ, XYBCZDMC, XYBCZDDM, XYBCZDRYSW, ZZZF, MJ, TBRQ, SCZT, CS_JYBZ, SCSJ) 
	SELECT
		A.JZLSH ZYLSH,     
		(select top 1 JBXX.GRJBXXBSH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = A.ZJHM) AS GRJBXXBSH,
		A.XGBZ AS XGBZ,
		(select top 1 JBXX.CXJMJKDABH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = A.ZJHM) AS CXJMJKDABH,
		A.ZYH AS ZYHBS,
		(select top 1 JBXX.JDRQ from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = A.ZJHM) AS CJSJ,
		A.CZYGH AS JDYSGH,
		A.CZYXM AS JDYSXM,
		'' AS BSCSZSFZH,
		'' AS YHZDGXDM,
		'' AS YHZDGXMC,
		'' AS BSCSZXM,
		isnull((SELECT SPTDM FROM SYS_ZCJG_DZ where A.YLJGDM=QPTDM),'500103000000001') AS YLJGDM,
		C.JGMC AS YLJGMC,
		'500103000000001' AS WDGLJGDM,
		'渝中区卫计委' AS WDGLJGMC,
		'' AS ZZSHRGH,
		'' AS ZZSHRXM,
		A.JZYSGH AS JZYSGH,
		A.JZYSXM AS JZYSXM,
		A.ZYYSGH AS ZYYSGH,
		A.ZYYSXM AS ZYYSXM,
		A.ZZYSGH AS ZZYSGH,
		A.ZZYSXM AS ZZYSXM,
		A.RYSJ AS RYSJ,
		A.BCH AS BCH,
		'' AS BCHMC,
		A.BFH AS BFH,
		'' AS BFHMC,
		A.RYKSBM AS RYKSBM,
		A.RYKSMC AS RYKSMC,
		A.RYBQBM AS RYBQBM,
		A.RYBQMC AS RYBQMC,
		isnull((SELECT SPTDM FROM SYS_ZCJG_DZ where A.YLJGDM=QPTDM),'500103000000001') AS JZYYBM,
		C.JGMC AS JZYYMC,
		F.ZS AS ZSMS,
		F.XBS AS XBSMS,
		'' AS JWSBZ,
		D.JBSHWS AS JWSMS,
		D.HZCRXBZ AS CRBBZ,
		D.CRBS AS CRBMS,
		E.HYS AS HYSMS,
		D.GMS AS GMSMS,
		D.SSS AS SSSMS,
		D.YFJZS AS YFJZSMS,
		D.SXS AS SXSMS,
		E.GRS AS GRSMS,
		E.YJS AS YJSMS,
		D.JZS AS JZSMS,
		D.TW AS RYTW,
		D.MLC AS RYMB,
		D.HXPLC AS RYHXPL,
		D.SSY AS RYSSY,
		D.SZY AS RYSZY,
		NULL AS RYSG,
		D.TZ AS RYTZ,
		'' AS YBZKJCJG,
		'' AS PFHNMJCJG,
		'' AS QSQMLBJCJG,
		'' AS TBJQQGJCJG,
		'' AS JBJCJG,
		'' AS XBJCJG,
		'' AS FBJCJG,
		'' AS GMZZJCJG,
		'' AS WSZQJCJG,
		'' AS JZJCJG,
		'' AS SZJCJG,
		'' AS SJXTJCJG,
		'' AS ZKQKMS,
		'' AS FZJCJG,
		'' AS CSNRKKBZ,
		F.JZRQSJ AS XYCZDRQ,
		F.XYZDDM AS XYCZDM,
		F.XYZDMC AS XYCZMC,
		'' AS XYCZRYZDSW,
		F.SZGCJG AS ZYSZGCJG,
		F.JZRQSJ AS ZYCZRQ,
		F.ZYBMMC AS ZYCZBM,
		F.ZYBMDM AS ZYCZBMDM,
		F.ZYZHDM AS ZYCZZHMC,
		F.ZYZHMC AS ZYCZZHDM,
		'' AS ZYCZRYZDSW,
		NULL AS XYXZZDRQ,
		'' AS XYXZZDMC,
		'' AS XYXZZDDM,
		NULL AS XYXZRYZDSW,
		NULL AS ZYXZZDRQ,
		'' AS ZYXZZDMC,
		'' AS ZYXZZDDM,
		'' AS ZYXZHZMC,
		'' AS ZYXZHZDM,
		NULL AS ZYXZRYZDSW,
		A.RYZDMC AS XYQDZDMC,
		NULL AS XYQDZDRQ,
		A.RYZDBM AS XYQDZDDM,
		NULL AS XYQZZDSW,
		A.RYSJ AS ZYQDZDRQ,
		'' AS ZYQDZDDM,
		'' AS ZYQDZDMC,
		'' AS ZYQDZHMC,
		'' AS ZYQDZHDM,
		'' AS ZYQZRYSW,
		NULL AS XYBCZDRQ,
		'' AS XYBCZDMC,
		'' AS XYBCZDDM,
		NULL AS XYBCZDRYSW,
		F.ZZZF AS ZZZF,
		A.MJ AS MJ,
		A.TBRQ AS TBRQ,
		'0' AS SCZT,
		0 AS CS_JYBZ,
		GETDATE() AS SCSJ
	FROM ZYRYDJB A(NOLOCK) 
	--INNER JOIN GXBZ_DA_GR_JBXX B(NOLOCK) ON A.ZJHM=B.ZJHM
	LEFT JOIN SYS_ZCJG_DZ C(NOLOCK) ON A.YLJGDM=C.QPTDM
	LEFT JOIN EMR_RYPGJL D(NOLOCK) ON A.YLJGDM=D.YLJGDM AND A.JZLSH=D.ZYJZLSH
	LEFT JOIN JBJKXX E(NOLOCK) ON A.YLJGDM=E.YLJGDM AND A.JZLSH=E.JZLSH
	LEFT JOIN EMR_MJZBL F(NOLOCK) ON A.YLJGDM=F.YLJGDM AND A.JZLSH=F.JZLSH
	WHERE a.TBRQ>ISNULL(@kssj,'1900-01-01')
		and 
	(select top 1 JBXX.GRJBXXBSH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = A.ZJHM) is not NULL

	--删除中间库中重复数据
	DELETE A FROM GXBZ_EMR_RYJL A,#TEMP_GXBZ_EMR_RYJL B
	WHERE A.GRJBXXBSH=B.GRJBXXBSH AND A.JZYYBM=B.JZYYBM AND A.ZYHBS=B.ZYHBS

	--插入数据至中间库
	INSERT INTO GXBZ_EMR_RYJL (ZYLSH,GRJBXXBSH, XGBZ, CXJMJKDABH, ZYHBS, CJSJ, JDYSGH, JDYSXM, BSCSZSFZH, YHZDGXDM, YHZDGXMC, BSCSZXM,
	  YLJGDM, YLJGMC, WDGLJGDM, WDGLJGMC, ZZSHRGH, ZZSHRXM, JZYSGH, JZYSXM, ZYYSGH, ZYYSXM, ZZYSGH, ZZYSXM, RYSJ, BCH, BCHMC, BFH,
	  BFHMC, RYKSBM, RYKSMC, RYBQBM, RYBQMC, JZYYBM, JZYYMC, ZSMS, XBSMS, JWSBZ, JWSMS, CRBBZ, CRBMS, HYSMS, GMSMS, SSSMS, YFJZSMS,
	  SXSMS, GRSMS, YJSMS, JZSMS, RYTW, RYMB, RYHXPL, RYSSY, RYSZY, RYSG, RYTZ, YBZKJCJG, PFHNMJCJG, QSQMLBJCJG, TBJQQGJCJG, JBJCJG,
	  XBJCJG, FBJCJG, GMZZJCJG, WSZQJCJG, JZJCJG, SZJCJG, SJXTJCJG, ZKQKMS, FZJCJG, CSNRKKBZ, XYCZDRQ, XYCZDM, XYCZMC, XYCZRYZDSW,
	  ZYSZGCJG, ZYCZRQ, ZYCZBM, ZYCZBMDM, ZYCZZHMC, ZYCZZHDM, ZYCZRYZDSW, XYXZZDRQ, XYXZZDMC, XYXZZDDM, XYXZRYZDSW, ZYXZZDRQ, ZYXZZDMC,
	  ZYXZZDDM, ZYXZHZMC, ZYXZHZDM, ZYXZRYZDSW, XYQDZDRQ, XYQDZDMC, XYQDZDDM, XYQZZDSW, ZYQDZDRQ, ZYQDZDDM, ZYQDZDMC, ZYQDZHMC, ZYQDZHDM,
	  ZYQZRYSW, XYBCZDRQ, XYBCZDMC, XYBCZDDM, XYBCZDRYSW, ZZZF, MJ, TBRQ, SCZT, CS_JYBZ, SCSJ)
	SELECT ZYLSH,GRJBXXBSH, XGBZ, CXJMJKDABH, ZYHBS, CJSJ, JDYSGH, JDYSXM, BSCSZSFZH, YHZDGXDM, YHZDGXMC, BSCSZXM, YLJGDM, YLJGMC,
	  WDGLJGDM, WDGLJGMC, ZZSHRGH, ZZSHRXM, JZYSGH, JZYSXM, ZYYSGH, ZYYSXM, ZZYSGH, ZZYSXM, RYSJ, BCH, BCHMC, BFH, BFHMC, RYKSBM, RYKSMC, RYBQBM,
		RYBQMC, JZYYBM, JZYYMC, ZSMS, XBSMS, JWSBZ, JWSMS, CRBBZ, CRBMS, HYSMS, GMSMS, SSSMS, YFJZSMS, SXSMS, GRSMS, YJSMS, JZSMS,
		RYTW, RYMB, RYHXPL, RYSSY, RYSZY, RYSG, RYTZ, YBZKJCJG, PFHNMJCJG, QSQMLBJCJG, TBJQQGJCJG, JBJCJG, XBJCJG, FBJCJG, GMZZJCJG,
		WSZQJCJG, JZJCJG, SZJCJG, SJXTJCJG, ZKQKMS, FZJCJG, CSNRKKBZ, XYCZDRQ, XYCZDM, XYCZMC, XYCZRYZDSW, ZYSZGCJG, ZYCZRQ, ZYCZBM,
		ZYCZBMDM, ZYCZZHMC, ZYCZZHDM, ZYCZRYZDSW, XYXZZDRQ, XYXZZDMC, XYXZZDDM, XYXZRYZDSW, ZYXZZDRQ, ZYXZZDMC, ZYXZZDDM, ZYXZHZMC,
		ZYXZHZDM, ZYXZRYZDSW, XYQDZDRQ, XYQDZDMC, XYQDZDDM, XYQZZDSW, ZYQDZDRQ, ZYQDZDDM, ZYQDZDMC, ZYQDZHMC, ZYQDZHDM, ZYQZRYSW,
		XYBCZDRQ, XYBCZDMC, XYBCZDDM, XYBCZDRYSW, ZZZF, MJ, TBRQ, SCZT, CS_JYBZ, SCSJ
	FROM #TEMP_GXBZ_EMR_RYJL

	COMMIT TRAN

	   SELECT 'T' AS SRESULT, '表：GXBZ_EMR_RYJL数据抽取成功！' AS SHINT
   
	   INSERT INTO SYS_SCSJ_LOG(CONTENT,TABLENAME,RECORDTIME,RST,PROCNAME) 
	   VALUES('表：GXBZ_EMR_RYJL数据抽取成功','GXBZ_EMR_RYJL',GETDATE(),'T','USP_GXBZ_EMR_RYJL')  
   
	   --INSERT INTO SYS_SJSC_TABLENAME(TABLE_NAME, SCKSSJ, SCJSSJ, BZ)
	   --VALUES('GXBZ_EMR_RYJL',@kssj,@jssj,'')
	   --更新上传时间
		UPDATE SYS_SJSC_TABLE SET TABLE_TIME = @jssj WHERE TABLE_NAME = 'GXBZ_EMR_RYJL'
		

	END TRY BEGIN CATCH
		SELECT 'F' AS SRESULT, '表：GXBZ_EMR_RYJL数据抽取失败！' AS SHINT, ERROR_MESSAGE() AS MSG
		ROLLBACK TRAN

		/*记录每步执行的记录日志*/
		INSERT INTO SYS_SCSJ_LOG(CONTENT,TABLENAME,RECORDTIME,RST,PROCNAME,MSG,ERRORCODE) 
		VALUES('表：GXBZ_EMR_RYJL数据抽取失败','GXBZ_EMR_RYJL',GETDATE(),'F','USP_GXBZ_EMR_RYJL',ERROR_MESSAGE(),ERROR_NUMBER())
	END CATCH
	END
END

